<?php
/***************************
MYSQL数据库操作类
lsj 2010-09-25
****************************/
if(!defined('APP_HOME')) exit('Access Denied');
class lib_class_mysql
{
    private $masterHost = null;#主服务器名、IP或别名
	private $masterUser = null;#主服务器用户名
	private $masterPaswd = null;#主服务器用户密码
	private $masterDbName = null;#主服务器数据库名
	private $masterCharSet = 'utf8';#主服务器数据库字符集

	private $slaveHost = null;#从服务器名、IP或别名
	private $slaveUser = null;#从服务器用户名
	private $slavePaswd = null;#从服务器用户密码
	private $slaveDbName = null;#从服务器数据库名
	private $slaveCharSet = 'utf8';#从服务器数据库字符集

	private $dbName = null;#数据库名
	private $charSet = 'utf8';#数据库字符集

	private $lk = false;#当前活动数据库连接标识符
	private $masterLK = false;#主数据库连接标识
	private $slaveLK = false;#从数据库连接标识
	private $queryId = false;#资源标识符
    private $queryString = '';#查询的SQL语句暂存处

    private $isSetChar = true;#是否已执行字符集设置
    private $isSelectDb = true;#是否已执行数据库选择
	private $isDebug = 'off';#调试状态
	private $isError = 'on';#报错开关


	/*
	#构造函数
	#可设置连接参数，也可不进行设置，参数为数组格式
	#声明一个实例的时候，默认不进行数据库连接，保证在没有操作的时候没有一丁点的资源开销
	*/
	function __construct($C = array())
	{
		if(!empty($C)){
			$this->setMasterHost($C);
			$this->setSlaveHost($C);
		}
	}

	/*
	#设置主服务器连接信息
	*/
	public function setMasterHost($C)
	{
		if(isset($C['Mhost'])) $this->masterHost = $C['Mhost'];
		if(isset($C['Muser'])) $this->masterUser = $C['Muser'];
		if(isset($C['Mpassword'])) $this->masterPaswd = $C['Mpassword'];
		if(isset($C['Mdbname'])) $this->masterDbName = $C['Mdbname'];
		if(isset($C['Mcharset'])) $this->charSet = $C['Mcharset'];
		return true;
	}

	/*
	#设置从服务器连接信息
	*/
	public function setSlaveHost($C)
	{
		if(isset($C['Shost'])) $this->slaveHost = $C['Shost'];
		if(isset($C['Suser'])) $this->slaveUser = $C['Suser'];
		if(isset($C['Spassword'])) $this->slavePaswd = $C['Spassword'];
		if(isset($C['Sdbname'])) $this->slaveDbName = $C['Sdbname'];
		if(isset($C['Scharset'])) $this->charSet = $C['Scharset'];
		return true;
	}

	/*
	* 连接DB
	*/
	public function connect($mod)
	{
		if('master' == $mod){#连接主服务器
			if($this->masterLK) return true;#已经连接
			if($this->masterHost === null ||
            $this->masterUser === null ||
            $this->masterPaswd === null ||
            $this->masterDbName === null){
				$this->showError(1);
			}
			$this->masterLK = @mysql_connect($this->masterHost, $this->masterUser, $this->masterPaswd);
			if(!$this->masterLK) $this->showError(3);
			if(!$this->selectDb($this->masterDbName, $this->masterLK)) $this->showError(4);
			if(!$this->setChar($this->masterCharSet, $this->masterLK)) $this->showError(5);
			return $this->masterLK;
		}

		if('slave' == $mod){#连接从服务器
			if($this->slaveLK) return true;#已经连接
			if($this->slaveHost === null ||
            $this->slaveUser === null ||
            $this->slavePaswd === null ||
            $this->slaveDbName === null){
				$this->showError(2);
			}
			$this->slaveLK = @mysql_connect($this->slaveHost, $this->slaveUser, $this->slavePaswd);
			if(!$this->slaveLK) $this->showError(6);
			if(!$this->selectDb($this->slaveDbName, $this->slaveLK)) $this->showError(7);
			if(!$this->setChar($this->slaveCharSet, $this->slaveLK)) $this->showError(8);
			return $this->slaveLK;
		}

		return false;
	}

	/*
	* 设置当前数据库的字符集
	*/
	public function setChar($charset, $lk = '')
	{
		$this->charSet = $charset;
		if($lk && is_resource($lk)){
			return @mysql_query("SET NAMES '".$this->charSet."'", $lk);
		}else{
			$this->isSetChar = false;#因为不知是哪个服务器，当执行SQL时设置字符集
		}
		return true;
	}

	/*
	* 选择当前操作的服务器上的数据库
	*/
	public function selectDb($dbName, $lk = '')
	{
		$this->dbName = $dbName;
		if($lk && is_resource($lk)){
			return @mysql_select_db($this->dbName, $lk);
		}else{
			$this->isSelectDb = false;#因为不知是哪个服务器，当执行SQL时选择数据库
		}
		return true;
	}

	/*
	* 设置报错开关
	*/
	public function setError($is)
	{
		if($is == 1 || strtolower($is) == "on" || $is == true){
			$this->isError = "on";
			return true;
		}
		if($is == 0 || strtolower($is) == "off" || $is == false){
			$this->isError = "off";
			return true;
		}
		return false;
	}

    /*
    * 设置调试状态
    */
    public function setDebug($is)
    {
        if($is == 1 || strtolower($is) == "on" || $is == true){
            $this->isDebug = "on";
            return true;
        }
        if($is == 0 || strtolower($is) == "off" || $is == false){
            $this->isDebug = "off";
            return true;
        }
        return false;
    }

    /*
    * 报错处理
    */
    public function showError($errno)
    {
        $errors = array();
        $errors[1] = 'Miss necessary master database connection parameters.';
        $errors[2] = 'Miss necessary slave database connection parameters.';
        if(!$errors[$errno]) $errors[$errno] = mysql_error();
        try{
	        if($this->isError == 'on'){
	            throw new Exception($errors[$errno]);
	        }else{
	            throw new Exception($errno);
	        }
		}catch(Exception $e){
			echo 'Mysql error: "'.$e->getMessage().'" in '.$_SERVER['SCRIPT_FILENAME']."\r\n";
			exit;
		}
        return null;
    }

	/*
	* 执行SQL语句
	*/
	public function query($sql)
	{
		$sql = str_replace(array("\t", "\r", "\n"), '', trim($sql));
        $this->queryString = strtolower($sql);
		$pos = strpos($this->queryString, 'select');

        #从服务器，用来查询
		if(0 === $pos){
			if($this->slaveHost){
				if(!$this->slaveLK) $this->connect('slave');
				$this->lk = $this->slaveLK;
			}else{#当从服务器不存在，就连接主服务器
				if(!$this->masterLK) $this->connect('master');
				$this->lk = $this->masterLK;
			}
		}else{
			if(!$this->masterLK) $this->connect('master');
			$this->lk = $this->masterLK;
		}

        #执行SQL
		if($this->lk){
			if(!$this->isSelectDb){
				$this->selectDb($this->dbName, $this->lk);
				$this->isSelectDb = true;
			}
			if(!$this->isSetChar){
				$this->setChar($this->charSet, $this->lk);
				$this->isSetChar = true;
			}
            if($this->isDebug == 'on') echo $sql."<br>\r\n";
			$this->queryId = @mysql_query($sql, $this->lk);
			if(false === $this->queryId) $this->showError(5);
			return $this->queryId;
		}

		return false;
	}

    /*
    * 从查询句柄获取结果集，与query方法配合使用
    * 返回一维数组
    */
    public function fetch($mod = 'array', $index = 'string')
    {
        if($this->queryId){
            $mod = strtolower($mod);
            $index = strtolower($index);
            #数组形式返回
            if('array' == $mod){
                $which = MYSQL_BOTH;
                if('string' == $index) $which = MYSQL_ASSOC;
                if('num' == $index) $whick = MYSQL_NUM;
                $result = mysql_fetch_array($this->queryId, $which);
            }
            #对象形式返回
            if('object' == $mod){
                $result = mysql_fetch_object($this->queryId);
            }
            $this->free();
            if($result)
                return $result;
            else
                return null;
        }
        return false;
    }

	/*
	* 获取结果集中第一行第一列的变量
	* 返回一个变量值
	*/
	public function selectOne($sql)
	{
		$this->query($sql);
		if($this->queryId){
			$result = mysql_fetch_row($this->queryId);
            $this->free();
            if($result[0])
                return $result[0];
            else
                return null;
		}
		return false;
	}

	/*
	* 获取结果集中第一行
	* $mod ：查询结果以数组["array"]　或　对象[“object”]形式返回
	* $index：查询结果以下标索引以字段名["string"]或数字[“num”]形式返回
	* 返回一维数组
	*/
	public function selectRow($sql, $mod = 'array', $index = 'string')
	{
		$this->query($sql);
		if($this->queryId){
			$mod = strtolower($mod);
			$index = strtolower($index);
			#数组形式返回
			if('array' == $mod){
				$which = MYSQL_BOTH;
				if('string' == $index) $which = MYSQL_ASSOC;
				if('num' == $index) $whick = MYSQL_NUM;
				$result = mysql_fetch_array($this->queryId, $which);
			}
			#对象形式返回
			if('object' == $mod){
				$result = mysql_fetch_object($this->queryId);
			}
            $this->free();
            if($result)
                return $result;
            else
                return null;
		}
		return false;
	}

	/*
	* 获取结果集全部结果
	* $mod ：查询结果以数组["array"]或对象["object"]形式返回
	* $index：查询结果以下标索引以字段名["string"]或数字[“num”]形式返回
	* 返回二维数组
	*/
	public function select($sql, $mod = 'array', $index = 'string')
	{
		$this->query($sql);
		if($this->queryId){
			$mod = strtolower($mod);
			$index = strtolower($index);
			$result = array();
			#数组形式返回
			if('array' == $mod){
				$which = MYSQL_BOTH;
				if('string' == $index) $which = MYSQL_ASSOC;
				if('num' == $index) $whick = MYSQL_NUM;
				while($res = mysql_fetch_array($this->queryId, $which)){
					$result[] = $res;
				}
			}
			#对象形式返回
			if('object' == $mod){
				while($res = mysql_fetch_object($this->queryId)){
					$result[] = $res;
				}
			}
            $this->free();
            if($result)
                return $result;
            else
                return null;
		}
		return false;
	}

	/*
	* 插入数据
	* $arr: 数组或字符串(构造好的SQL语句)
	* $tbl: 要操作的表名，当$arr为字符时，表名为空就可
	* 返回最新插入记录的ID或影响的条数
	*/
	public function insert($arr, $tbl = '')
	{
		$sql = '';
        $sqlKey = 'INSERT INTO `'.$tbl.'`(';
        $sqlValue = ') VALUES(';
		if(is_array($arr) && count($arr) > 0){
			foreach($arr as $key=>$val){
				$key = str_replace('`', '', $key);
				$sqlKey .= '`' . $key.'`,';
				$sqlValue .= "'" . mysql_escape_string($val) . "',";
			}
			$sqlKey = rtrim($sqlKey, ',');
			$sqlValue = rtrim($sqlValue, ',');
			$sql = $sqlKey . $sqlValue . ')';
		}elseif($arr && is_string($arr)){
            if($tbl)
                $sql = $sqlKey . $arr . $sqlValue .')';
			else
                $sql = $arr;
		}

		if($this->query($sql)){
			$lastId = $this->getInsertId();
			if($lastId)
                return $lastId;
			return $this->getAffectedRows();
		}

		return false;
	}

	/*
	* 更新数据
	* $arr: 数组或字符串(构造好的SQL语句)
	* $tbl: 要操作的表名，当$arr为字符时，表名为空就可
    * $where: 条件语句
	* 返回影响的条数
	*/
	public function update($arr, $tbl = '', $where = '1')
	{
		$sql = 'UPDATE `' . $tbl . '` SET ';
		if(is_array($arr) && count($arr) > 0){
			foreach($arr as $key=>$val){
				$val = str_replace('`', '', $val);
				$key = str_replace('`', '', $key);
				$sql .= '`' . $key . "`='" . mysql_escape_string($val) . "',";
				
				/*在被修改的值中可以用运算符
				if(eregi("^{:}.+{;}$", $val)){
					$val = str_replace(array('{:}', '{;}'), '', $val);
					$sql .= '`' . $key . "`=" . $val . ",";
				}else{
					$sql .= '`' . $key . "`='" . mysql_escape_string($val) . "',";
				}
				*/
			}
			$sql = rtrim($sql, ',');
		}elseif(is_string($arr)){
            $sql .= $arr;
		}
		
		$sql .= ' WHERE ' . $where;

		if($this->query($sql)){
			return $this->getAffectedRows();	
		}

        return false;
	}

	/*
	* 自动构造符合上次WHERE条件的SQL语句，查询出记录总条数
    * 返回符合条件的记录总数
	*/
	public function count($sql = '')
	{
		if($sql)
            return $this->selectOne($sql);
        if(!$this->queryString)
            return false;

		$from = strpos($this->queryString, " from ");

		if($ps = strpos($this->queryString, " distinct ")){
			preg_match("/select ([^,]*)[,| ]/", str_replace(' distinct', '', $this->queryString), $sel);
			$sel = 'COUNT(DISTINCT '.trim($sel[1]).') AS cnt';
		}else if($ps = strpos($this->queryString, " group ")){
			preg_match("/group\s+by\s+([^\s]*)\s/", $this->queryString, $sel);
			$sel = 'COUNT(DISTINCT '.trim($sel[1]).') AS cnt';
			$too[] = $ps;
		}else{
			$sel = 'COUNT(*) AS cnt';
		}

		if($ps = strpos($this->queryString, " limit ")) $too[] = $ps ;
		if(isset($too)) $to = min($too);
		if(!isset($to)) $to = strlen($this->queryString);
		$countQuery = "SELECT ".$sel." ".substr($this->queryString, $from, $to - $from);
		return $this->selectOne($countQuery);
	}

	/*
	* 获取当前被执行的SQL语句
	*/
	public function getQueryString($print = 0)
	{
		if($print)
        	echo $this->queryString."<br>\r\n";
		return $this->queryString;
	}

	/*
	* 获取插入最后一条记录的ID
	*/
	public function getInsertId()
	{
        if($this->lk && is_resource($this->lk))
		    return mysql_insert_id($this->lk);
        return false;
	}

	/*
	* 获取执行数据库操作所影响的记录条数
	*/
	public function getAffectedRows()
	{
		if($this->lk && is_resource($this->lk))
            return mysql_affected_rows($this->lk);
        return false;
	}

	/*
	* 释放结果集
	*/
	public function free()
	{
		if($this->queryId && is_resource($this->queryId)){
            $flag = mysql_free_result($this->queryId);
		    $this->queryId = false;
            return $flag;
        }
		return false;
	}

	/*
	* 关闭数据库
	* 关闭数据库,通常不需要使用，因为已打开的非持久连接会在脚本执行完毕后自动关闭
	*/
	public function close()
	{
        if($this->lk && is_resource($this->lk))
		    return mysql_close($this->lk);
        return false;
	}
}





?>
